﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using BPM.ReportDesigner.AjaxPage;

namespace BPM.ReportDesigner.GenerateReportFiles
{
    public partial class Template_20130809041723 : System.Web.UI.Page
    {
        private string UserName = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            UserName = new GetUserInfo().tttt();
            if (!IsPostBack)
            {
                BingData();
            }
        }

        public void BingData()
        {
             int PageIndex = AspNetPager1.CurrentPageIndex - 1; 
int PageSize = AspNetPager1.PageSize; 
string strWhere = ""; 
if (RESOURCEID.Text.Trim() != "") 
{ 
    strWhere += " AND RESOURCEID = '" + RESOURCEID.Text.Trim() + "' "; 
} 
if (CNNAME.Text.Trim() != "") 
{ 
    strWhere += " AND CNNAME = '" + CNNAME.Text.Trim() + "' "; 
} 
 
AspNetPager1.RecordCount = PageCountQuery(strWhere); 
string sql="select * from (select  row_number() over(order by COM_RESOURCE.RESOURCEID ASC) as rowid,COM_RESOURCE.RESOURCEID AS 'RESOURCEID1',COM_RESOURCE.CNNAME AS 'CNNAME',COM_RESOURCE.ENNAME AS 'ENNAME',COM_RESOURCE.TYPE AS 'TYPE',COM_RESOURCE.MODULE AS 'MODULE',COM_RESOURCE.CODE AS 'CODE',COM_RESOURCE.PARENTID AS 'PARENTID',COM_RESOURCE.REMARK AS 'REMARK',COM_RESOURCE.ORDERNO AS 'ORDERNO',COM_RESOURCE.ISACTIVE AS 'ISACTIVE',COM_RESOURCE.CREATEDATE AS 'CREATEDATE',COM_RESOURCE.CREATEBY AS 'CREATEBY',COM_RESOURCE.UPDATEDATE AS 'UPDATEDATE',COM_RESOURCE.UPDATEBY AS 'UPDATEBY',COM_RESOURCE.EFFECTFROM AS 'EFFECTFROM',COM_RESOURCE.EFFECTTO AS 'EFFECTTO',COM_RESOURCE.EXT01 AS 'EXT01',COM_RESOURCE.EXT02 AS 'EXT02',COM_RESOURCE.EXT03 AS 'EXT03',COM_RESOURCE.EXT04 AS 'EXT04',COM_RESOURCE.EXT05 AS 'EXT05',COM_RESOURCE.EXT06 AS 'EXT06',COM_RESOURCE.EXT07 AS 'EXT07',COM_RESOURCE.EXT08 AS 'EXT08',COM_RESOURCE.EXT09 AS 'EXT09',COM_RESOURCE.EXT10 AS 'EXT10',COM_RESOURCE.EXT11 AS 'EXT11',COM_RESOURCE.EXT12 AS 'EXT12',COM_RESOURCE.EXT13 AS 'EXT13',COM_RESOURCE.EXT14 AS 'EXT14',COM_RESOURCE.EXT15 AS 'EXT15',COM_RESOURCE.EXT16 AS 'EXT16',COM_RESOURCE.EXT17 AS 'EXT17',COM_RESOURCE.EXT18 AS 'EXT18',COM_RESOURCE.EXT19 AS 'EXT19',COM_RESOURCE.EXT20 AS 'EXT20',COM_RESOURCE.EXT21 AS 'EXT21',COM_RESOURCE.EXT22 AS 'EXT22',COM_RESOURCE.EXT23 AS 'EXT23',COM_RESOURCE.EXT24 AS 'EXT24',COM_RESOURCE.EXT25 AS 'EXT25',COM_RESOURCE.EXT26 AS 'EXT26',COM_RESOURCE.EXT27 AS 'EXT27',COM_RESOURCE.EXT28 AS 'EXT28',COM_RESOURCE.EXT29 AS 'EXT29',COM_RESOURCE.EXT30 AS 'EXT30' FROM COM_RESOURCE where 1=1  "+strWhere+" ) a WHERE (a.rowid > ("+AspNetPager1.CurrentPageIndex+"-1) *10 and a.rowid <= "+AspNetPager1.CurrentPageIndex+"*10)"; 
DataSet ds = ExecuteNonQuery(sql); 
ReportList.DataSource = ds; 
ReportList.DataBind(); 

        }

        protected void AspNetPager1_PageChanged(object sender, EventArgs e) 
{ 
  BingData(); 
} 

public int PageCountQuery(string strWhere) 
{ 
DataSet ds = ExecuteNonQuery("SELECT count(*) FROM COM_RESOURCE WHERE 1=1  "+strWhere+" "); 
return int.Parse(ds.Tables[0].Rows[0][0].ToString()); 
} 


        public void SearchButton_Click(object sender, EventArgs e) 
{ 
     BingData(); 
} 


        protected void ExportToExcel_Click(object sender, EventArgs e)
        {
            string strWhere = ""; 
if (RESOURCEID.Text.Trim() != "") 
{ 
    strWhere += " AND RESOURCEID = '" + RESOURCEID.Text.Trim() + "' "; 
} 
if (CNNAME.Text.Trim() != "") 
{ 
    strWhere += " AND CNNAME = '" + CNNAME.Text.Trim() + "' "; 
} 
 
DataTable tb = ExecuteNonQuery("SELECT COM_RESOURCE.RESOURCEID AS 'RESOURCEID1',COM_RESOURCE.CNNAME AS 'CNNAME',COM_RESOURCE.ENNAME AS 'ENNAME',COM_RESOURCE.TYPE AS 'TYPE',COM_RESOURCE.MODULE AS 'MODULE',COM_RESOURCE.CODE AS 'CODE',COM_RESOURCE.PARENTID AS 'PARENTID',COM_RESOURCE.REMARK AS 'REMARK',COM_RESOURCE.ORDERNO AS 'ORDERNO',COM_RESOURCE.ISACTIVE AS 'ISACTIVE',COM_RESOURCE.CREATEDATE AS 'CREATEDATE',COM_RESOURCE.CREATEBY AS 'CREATEBY',COM_RESOURCE.UPDATEDATE AS 'UPDATEDATE',COM_RESOURCE.UPDATEBY AS 'UPDATEBY',COM_RESOURCE.EFFECTFROM AS 'EFFECTFROM',COM_RESOURCE.EFFECTTO AS 'EFFECTTO',COM_RESOURCE.EXT01 AS 'EXT01',COM_RESOURCE.EXT02 AS 'EXT02',COM_RESOURCE.EXT03 AS 'EXT03',COM_RESOURCE.EXT04 AS 'EXT04',COM_RESOURCE.EXT05 AS 'EXT05',COM_RESOURCE.EXT06 AS 'EXT06',COM_RESOURCE.EXT07 AS 'EXT07',COM_RESOURCE.EXT08 AS 'EXT08',COM_RESOURCE.EXT09 AS 'EXT09',COM_RESOURCE.EXT10 AS 'EXT10',COM_RESOURCE.EXT11 AS 'EXT11',COM_RESOURCE.EXT12 AS 'EXT12',COM_RESOURCE.EXT13 AS 'EXT13',COM_RESOURCE.EXT14 AS 'EXT14',COM_RESOURCE.EXT15 AS 'EXT15',COM_RESOURCE.EXT16 AS 'EXT16',COM_RESOURCE.EXT17 AS 'EXT17',COM_RESOURCE.EXT18 AS 'EXT18',COM_RESOURCE.EXT19 AS 'EXT19',COM_RESOURCE.EXT20 AS 'EXT20',COM_RESOURCE.EXT21 AS 'EXT21',COM_RESOURCE.EXT22 AS 'EXT22',COM_RESOURCE.EXT23 AS 'EXT23',COM_RESOURCE.EXT24 AS 'EXT24',COM_RESOURCE.EXT25 AS 'EXT25',COM_RESOURCE.EXT26 AS 'EXT26',COM_RESOURCE.EXT27 AS 'EXT27',COM_RESOURCE.EXT28 AS 'EXT28',COM_RESOURCE.EXT29 AS 'EXT29',COM_RESOURCE.EXT30 AS 'EXT30' FROM COM_RESOURCE WHERE 1=1  "+strWhere+"  order by COM_RESOURCE.RESOURCEID ASC").Tables[0]; 
Response.Clear(); 
Response.Charset = "GB2312"; 
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 
Response.ContentType = "application/ms-excel"; 
Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(DateTime.Now.ToShortDateString(), System.Text.Encoding.GetEncoding("GB2312")) + ".xls"); 
CultureInfo cult = new CultureInfo("zh-CN", true); 
StringWriter sw = new StringWriter(cult); 
HtmlTextWriter htw = new HtmlTextWriter(sw); 
DataGrid dgrid = new DataGrid(); 
dgrid.DataSource = tb.DefaultView; 
dgrid.AllowPaging = false; 
dgrid.DataBind(); 
htw.WriteLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=GB2312\">"); 
dgrid.RenderControl(htw); 
Response.Write(sw.ToString()); 
Response.End(); 

        }

        public DataSet ExecuteNonQuery(string sql) 
{ 
     using(OleDbConnection oleConn=new OleDbConnection("Provider=SQLOLEDB.1;User ID=sa;Password=sa;Initial Catalog=UltimusBusiness;Data Source=.")) 
     { 
         using (OleDbCommand cmd = new OleDbCommand(sql, oleConn)) 
         { 
             using (OleDbDataAdapter oda = new OleDbDataAdapter(cmd)) 
             { 
                 DataSet ds = new DataSet(); 
                 oda.Fill(ds); 
                 return ds; 
             } 
         } 
     } 
} 


    }
}